#-*- conding: encoding -*-
#!/usr/bin/python3
'''
Author: ruanchao
Date: 2021-11-18 15:29:14
LastEditTime: 2021-12-02 19:35:55
LastEditors: ruanchao
Description: 获取龙虎榜数据
FilePath: \stock-analysis-python\stockHelper.py
'''
from datetime import date, timedelta, datetime
from mysqlHelper import mysqlHelper
from utils import get_trade_dates
import smtplib
from email.mime.text import MIMEText
from email.header import Header

class stockHelper:
    def __init__(self):
        self.current_day = datetime.now().strftime("%Y-%m-%d")

    # 格式化code为雪球的code
    def formatXueQiuCode(self, code):
        # 将sz.转换成SZ，sh.转换成SH
        code = code.replace('sz.', 'SZ')
        code = code.replace('sh.', 'SH')
        return code

    # 分析数据
    # 1. 查找金针探底的票。也就是收盘价和最低价相差比较大的票。
    # 例如一只票10元，跌5%就是9.5元，收盘10元，这样的就是金针探底。
    # 我们可以把 (收盘价-最低价) / 收盘价 >= 5% and (开盘价-最低价) / 开盘价 >= 5% 的当作金针探底
    def analysis_data(self):
        trade_days = get_trade_dates()
        email_title = "执行的{date}金针探底的数据如下:".format(date = self.current_day)
        email_content = ""
        if len(trade_days) > 0:
            trade_days = [trade_days[len(trade_days) - 1]]
            for day in trade_days:
                email_content += self.get_jin_zhen_tan_di_stock(day)
        else:
            email_content += "<h3>没有获取到交易日数据</h3>"
        self.send_email(email_title, email_content)



    # 金针探底的方法
    def get_jin_zhen_tan_di_stock(self, day):
        db = mysqlHelper()
        sql = "select a.*, ((a.open - a.low) / a.open) as 'open_subtract_price_percent', ((a.close - a.low) / a.close) as 'close_subtract_price_percent', b.code_name from `stock_list_baostock` as a left join `stock_basic_baostock` as b on a.code = b.code where a.date='{}' and (a.open - a.low) / a.open > 0.05 and (a.close - a.low) / a.close > 0.05 and a.pctChg != '' and a.isST = 0".format(day)
        res = db.select(sql)
        email_content = "<h3>(收盘价-最低价) / 收盘价 >= 5% and (开盘价-最低价) / 开盘价 >= 5%</h3>"
        if (len(res)):
            email_content += "<div style='margin: 20px;'><table border='1' cellpadding='10' frame='void'><caption>{}数据</caption><tr><th>序号</th><th>日期</th><th>代码</th><th>代码名称</th><th>开盘价</th><th>最低价</th><th>收盘价</th><th>最高价</th><th>最低价与开盘价差比为</th><th>最低价与收盘价差比为</th><th>成交量(股)</th><th>成交额(元)</th><th>换手率(百分比)</th><th>涨跌幅</th></tr>".format(day)
            serial_number = 0
            for item in res:
                self.add_jin_zhen_tan_di_data_to_db(item)
                serial_number = serial_number + 1
                amount = float(item["amount"])
                volume = int(item["volume"])
                turn = float(item["turn"])
                pctChg = float(item["pctChg"])
                date = item["date"].strftime('%Y-%m-%d')
                email_content += "<tr><td>{serial_number}</td><td>{date}</td><td><a href='https://xueqiu.com/S/{formatXueQiuCode}' target='_blank'>{code}</a></td><td>{code_name}</td><td>{open}</td><td>{low}</td><td>{close}</td><td>{high}</td><td>{open_subtract_price_percent}%</td><td>{close_subtract_price_percent}%</td><td>{volume}</td><td>{amount}</td><td>{turn}%</td><td>{pctChg}%</td></tr>".format(serial_number=serial_number, date=date, formatXueQiuCode=self.formatXueQiuCode(item["code"]), code=item["code"], code_name=item["code_name"], open=item["open"], low=item["low"], close=item["close"], high=item["high"], open_subtract_price_percent=round(item["open_subtract_price_percent"] *100, 2), close_subtract_price_percent=round(item["close_subtract_price_percent"] * 100, 2), volume=volume, amount=self.format_amount(amount), turn=round(turn , 2), pctChg=round(pctChg , 2)  )
            email_content += "</table></div>"
        else:
            email_content += "<h3>没有金针探底的数据</h3>"
        return email_content

    # 将金针探底数据插入到数据库做一下保存
    def add_jin_zhen_tan_di_data_to_db(self, item):
        sql = "INSERT INTO `stock_list_jinzhen_baostock` (code,code_name,close,date) VALUES ('{code}','{code_name}','{close}','{date}')".format(code=item["code"], code_name=item["code_name"], close=item["close"], date=item["date"])
        db = mysqlHelper()
        db.execute_sql(sql)
        return

    # 连续N天大涨的数据
    def get_n_days_rise_stock(self, days):
        db = mysqlHelper()
        sql = "select a.*, b.code_name from `stock_list_baostock` as a left join `stock_basic_baostock` as b on a.code = b.code where a.pctChg != '' and a.isST = 0 and b.type = 1 and a.date IN( SELECT t.date FROM (SELECT date FROM stock_list_baostock GROUP BY date ORDER BY date DESC LIMIT {days} ) t) ORDER BY date ASC".format(days = days)
        res = db.select(sql)
        code_dict = {}
        if len(res):
            # 处理一下数据，将同一个code的数据清理出来
            for item in res:
                code = item["code"]
                if code not in code_dict:
                    code_dict[code] = []
                code_dict[code].append(item)
        code_list = []
        if (len(code_dict)):
            for code in code_dict:
                list = code_dict[code]
                is_grow_list = []
                days_chg = 0
                for item in list:
                    pct_chg = float(item["pctChg"])
                    days_chg = days_chg + pct_chg
                    if pct_chg > 0:
                        is_grow_list.append(1)
                    else:
                        is_grow_list.append(0)
                if 0 not in is_grow_list and days_chg > 7:
                    item = list[len(list)-1]
                    item["days_chg"] = days_chg
                    code_list.append(item)
        if len(code_list):
            code_list = sorted(code_list,key = lambda e:e.__getitem__('days_chg'), reverse = True)
            email_title = "{date}执行本月的连续{days}日上涨的数据如下:".format(date = self.current_day, days = days)
            email_content = "<div style='margin: 20px;'><table border='1' cellpadding='10' frame='void'><caption>本月{}查询连续三日上涨数据</caption><tr><th>序号</th><th>日期</th><th>代码</th><th>代码名称</th><th>开盘价</th><th>最低价</th><th>收盘价</th><th>最高价</th><th>成交量(股)</th><th>成交额(元)</th><th>换手率(百分比)</th><th>涨跌幅</th><th>三日涨跌幅</th></tr>".format(self.current_day)
            serial_number = 0
            for item in code_list:
                if item["code_name"] is None:
                    continue
                serial_number = serial_number + 1
                amount = float(item["amount"])
                volume = int(item["volume"])
                turn = float(item["turn"])
                pctChg = float(item["pctChg"])
                days_chg = float(item["days_chg"])
                date = item["date"].strftime('%Y-%m-%d')
                email_content += "<tr><td>{serial_number}</td><td>{date}</td><td><a href='https://xueqiu.com/S/{formatXueQiuCode}' target='_blank'>{code}</a></td><td>{code_name}</td><td>{open}</td><td>{low}</td><td>{close}</td><td>{high}</td><td>{volume}</td><td>{amount}</td><td>{turn}%</td><td>{pctChg}%</td><td>{days_chg}%</td></tr>".format(serial_number = serial_number,date=date, formatXueQiuCode=self.formatXueQiuCode(item["code"]), code=item["code"], code_name=item["code_name"], open=item["open"], low=item["low"], close=item["close"], high=item["high"], volume=volume, amount=self.format_amount(amount), turn=round(turn , 2), pctChg=round(pctChg , 2),days_chg=round(days_chg , 2)  )
            email_content += "</table></div>"
            self.send_email(email_title, email_content)
        return

    # 获取某一天成交量放大的股票，并且成交量是前面N天平均值的几倍的票
    def get_volume_rise_N_times_stock(self, days, times):
        if days < 2:
            return
        db = mysqlHelper()
        sql = "WITH CTE AS (SELECT a.*, b.code_name, ROW_NUMBER() OVER (PARTITION BY a.code ORDER BY a.date DESC) AS rn FROM ( SELECT *, AVG(volume) OVER (PARTITION BY code ORDER BY date ROWS BETWEEN {days} PRECEDING AND 1 PRECEDING) AS avg_volume FROM stock_list_baostock ) a LEFT JOIN stock_basic_baostock b ON a.code = b.code WHERE a.volume >= a.avg_volume * {times} AND a.date >= DATE_ADD(CURRENT_DATE, INTERVAL -1 MONTH) ) SELECT * FROM CTE WHERE rn = 1;".format(days = days, times = times)
        res = db.select(sql)
        code_list = []
        if len(res):
            # 处理一下数据，将同一个code的数据清理出来
            for item in res:
                code_list.append(item)
        if len(code_list):
            code_list = sorted(code_list,key = lambda e:e.__getitem__('volume'), reverse = True)
            email_title = "{date}执行一个月内成交量是前面{days}天的平均值{times}倍的数据如下:".format(date = self.current_day, days = days, times = times)
            email_content = "<div style='margin: 20px;'><table border='1' cellpadding='10' frame='void'><caption>{date}执行一个月内成交量是前面{days}天的平均值{times}倍的数据如下</caption><tr><th>序号</th><th>日期</th><th>代码</th><th>代码名称</th><th>开盘价</th><th>最低价</th><th>收盘价</th><th>最高价</th><th>成交量(股)</th><th>平均成交量(股)/{days}天</th><th>成交额(元)</th><th>换手率(百分比)</th><th>涨跌幅</th></tr>".format(date = self.current_day, days = days, times = times)
            serial_number = 0
            for item in code_list:
                if item["code_name"] is None:
                    continue
                serial_number = serial_number + 1
                amount = float(item["amount"])
                volume = int(item["volume"])
                avg_volume = int(item["avg_volume"])
                turn = float(item["turn"])
                pctChg = float(item["pctChg"])
                date = item["date"].strftime('%Y-%m-%d')
                email_content += "<tr><td>{serial_number}</td><td>{date}</td><td><a href='https://xueqiu.com/S/{formatXueQiuCode}' target='_blank'>{code}</a></td><td>{code_name}</td><td>{open}</td><td>{low}</td><td>{close}</td><td>{high}</td><td>{volume}</td><td>{avg_volume}</td><td>{amount}</td><td>{turn}%</td><td>{pctChg}%</td></tr>".format(serial_number = serial_number,date=date, formatXueQiuCode=self.formatXueQiuCode(item["code"]), code=item["code"], code_name=item["code_name"], open=item["open"], low=item["low"], close=item["close"], high=item["high"], volume=volume, avg_volume=avg_volume,amount=self.format_amount(amount), turn=round(turn , 2), pctChg=round(pctChg , 2))
            email_content += "</table></div>"
            self.send_email(email_title, email_content)
        return

    # 获取大涨之后，连续几天回调，并且最低价格没有跌破大涨那天的最低价的数据
    def get_rise_callback_stock(self):
        db = mysqlHelper()
        sql = "select a.*, b.code_name from `stock_list_baostock` as a left join `stock_basic_baostock` as b on a.code = b.code where a.pctChg != '' and a.isST = 0 and b.type = 1 and a.date IN( SELECT t.date FROM (SELECT date FROM stock_list_baostock GROUP BY date ORDER BY date) t) ORDER BY date ASC"
        res = db.select(sql)
        # 查询的所有数据，然后代码进行分析
        code_dict = {}
        if len(res):
            # 处理一下数据，将同一个code的数据清理出来
            for item in res:
                code = item["code"]
                if code not in code_dict:
                    code_dict[code] = []
                code_dict[code].append(item)
        code_list = []
        if (len(code_dict)):
            for code in code_dict:
                # 对应code的股票数据，按照日期排列
                list = code_dict[code]
                # 获取当前股票有某一天大涨，然后后面的有回调的数据
                grow_list = self.grow_and_callback_data(list)
                # 如果没有大涨之后有回调的数据，那么直接跳过
                if not grow_list:
                    continue
                # 获取最后一天的数据
                item = grow_list[len(grow_list) - 1]
                # 将最后一天的数据添加到code_list中
                code_list.append(item)
        email_title = "{date}执行本月的大涨之后，连续五天回调，并且最低价格没有跌破大涨那天的最低价的数据如下:".format(date = self.current_day)
        if len(code_list):
            code_list = sorted(code_list,key = lambda e:e.__getitem__('date'), reverse = True)
            email_content = "<div style='margin: 20px;'><table border='1' cellpadding='10' frame='void'><caption>本月{}查询大涨之后，连续几天回调，并且最低价格没有跌破大涨那天的最低价的数据</caption><tr><th>序号</th><th>日期</th><th>代码</th><th>代码名称</th><th>开盘价</th><th>最低价</th><th>收盘价</th><th>最高价</th><th>成交量(股)</th><th>成交额(元)</th><th>换手率(百分比)</th><th>涨跌幅</th></tr>".format(self.current_day)
            serial_number = 0
            for item in code_list:
                if item["code_name"] is None:
                    continue
                serial_number = serial_number + 1
                amount = float(item["amount"])
                volume = int(item["volume"])
                turn = float(item["turn"])
                pctChg = float(item["pctChg"])
                date = item["date"].strftime('%Y-%m-%d')
                email_content += "<tr><td>{serial_number}</td><td>{date}</td><td><a href='https://xueqiu.com/S/{formatXueQiuCode}' target='_blank'>{code}</a></td><td>{code_name}</td><td>{open}</td><td>{low}</td><td>{close}</td><td>{high}</td><td>{volume}</td><td>{amount}</td><td>{turn}%</td><td>{pctChg}%</td></tr>".format(serial_number = serial_number,date=date, formatXueQiuCode=self.formatXueQiuCode(item["code"]), code=item["code"], code_name=item["code_name"], open=item["open"], low=item["low"], close=item["close"], high=item["high"], volume=volume, amount=self.format_amount(amount), turn=round(turn , 2), pctChg=round(pctChg , 2)  )
            email_content += "</table></div>"
        else:
            email_content = "<h3>没有大涨之后，连续五天回调，并且最低价格没有跌破大涨那天的最低价的数据</h3>"
        self.send_email(email_title, email_content)
        return


    # 根据list获取其中大涨了之后有回调的数据
    def grow_and_callback_data(self, list):
        if len(list) < 5:
            return []
        # 获取其中有一天大涨的数据，如果是创业板的，那么大涨就是涨幅超过16%，如果是主板的，那么大涨就是涨幅超过8%,筛选对应的数据
        grow_list = [item for item in list if self.is_daZhang(item)]
        # 如果没有大涨的数据，那么直接返回空数组
        if not grow_list:
            return []
        # 定义当前存储的code_list
        code_list = []
        # 选取高新发展的票做测试
        # grow_list = [item for item in grow_list if item["code"] == "sz.000628"]
        # 获取了大涨数据之后，进行循环遍历，获取大涨之后的回调数据
        for item in grow_list:
            # 取筛选后的前面五条数据，因为大涨之后的回调，最多回调五天
            after_list = [list_item for list_item in list if list_item["date"] > item["date"]][0:5]
            if not after_list:
                continue
            # 获取大涨那天的最低价
            low_price = item["low"]
            # 获取大涨那天的最高价
            high_price = item["high"]

            # 遍历after_list, 判断后面几天的最低价是否低于大涨那天的最低价
            for after_item in after_list:
                # 如果有一天的最低价低于大涨那天的最低价，那么就不符合条件，直接跳出循环
                if after_item["low"] < low_price:
                    break
                # 如果最后一天的最低价都大于大涨那天的最低价，那么就是符合条件的
                if after_item["low"] > low_price:
                    code_list.append(after_item)
        # 返回code_list
        return code_list


    def is_daZhang(self, item):
        code = item["code"]
        if self.check_is_chuangYeBan(code):
            return float(item["pctChg"]) > 16
        elif self.check_is_zhuBan(code):
            return float(item["pctChg"]) > 8
        else:
            return False

    def check_is_chuangYeBan(self, code):
        return code.startswith("sz.300")

    def check_is_zhuBan(self, code):
        return code.startswith("sh.600") or code.startswith("sz.000")

    # 格式化价格
    def format_amount(self, amount):
        formated_amount = 0
        if amount > 100000000:
            formated_amount = int(amount / 100000000)
            return str(formated_amount) + "亿"
        elif amount > 10000:
            formated_amount = int(amount / 10000)
            return str(formated_amount) + "万"
        else:
            formated_amount = amount
            return str(formated_amount) + "元"


    def send_email(self, title, content):
        mail_host = 'smtp.qq.com'  # 腾讯smtp服务器
        mail_user = '656817717@qq.com'    # 需要登录的邮箱账号
        mail_pass = 'qpgswbfikmmobdia'  # 对应邮箱密码也就是授权码,需要开启smtp 并启用授权码,代码后面会有提示

        message = MIMEText(content, 'html', 'utf-8')  # 邮件内容

        senderOne = '656817717@qq.com'  # 发件人邮箱(mail_user = 'xx@qq.com')

        receiversOne = [
            "ruanchao1993@163.com", # 自己
            "178113299@qq.com", # 鲁嘉骏
            "616596426@qq.com", # 陈然
            "408876917@qq.com", # 猫哥
            "935688949@qq.com", # 驴哥
            "641246913@qq.com", # 阿祖
            "331617870@qq.com", # 龙骑
            "723983400@qq.com", # zeno
            "617371914@qq.com", # 火
            "767903423@qq.com", # 亮
            "1277163485@qq.com", # 刘得志
            "552031224@qq.com", # 11
        ]  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱
        # receiversOne = ['ruanchao1993@163.com']
        message['From'] = "{}".format(senderOne)
        message['To'] = ",".join(receiversOne)

        message['Subject'] = title

        try:
            smtpObj = smtplib.SMTP_SSL(mail_host, 465)  # 启用SSL发信, 端口一般是465
            smtpObj.login(mail_user, mail_pass)  # 登录验证
            smtpObj.sendmail(senderOne, receiversOne, message.as_string())  # 发送
            print("邮件发送成功")
        except smtplib.SMTPException as e:
            print("邮件发送失败", e)